<?xml version="1.0" ?>

<xsl:stylesheet   xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
						xmlns:lzc="http://schemas.icitadel.com/Lazarus/Compiled.xsd"
                  version="1.0">

	<xsl:output method="text"/>

	<xsl:include href="../../common.xsl" />
	<xsl:include href="../../convert.xsl" />

	<xsl:template match="lzc:project">
		<xsl:apply-templates select="//lzc:method[ ( @compiling ) ]" />
	</xsl:template>

	<xsl:template match="lzc:method[ @compiling ]">
		<xsl:text>-- #lazgen HEADER#
/*
	sp</xsl:text>
		<xsl:value-of select="@db-name" />
		<xsl:text>.

	</xsl:text>

		<xsl:choose>
			<xsl:when test="@methodtype = 'Get'">
				<xsl:text>This procedure returns a single </xsl:text>
				<xsl:value-of select="ancestor::lzc:table/@friendly-name" />
				<xsl:text> from the database, based on a specific primary key value.</xsl:text>
			</xsl:when>
			<xsl:when test="@methodtype = 'List'">
				<xsl:text>This procedure returns a list of all </xsl:text>
				<xsl:value-of select="ancestor::lzc:table/@friendly-name" />
				<xsl:text> from the database.</xsl:text>
			</xsl:when>
			<xsl:when test="@methodtype = 'Update'">
				<xsl:text>This procedure updates a single </xsl:text>
				<xsl:value-of select="ancestor::lzc:table/@friendly-name" />
				<xsl:text> in the database.</xsl:text>
			</xsl:when>
			<xsl:when test="@methodtype = 'Create'">
				<xsl:text>This procedure creates a new single </xsl:text>
				<xsl:value-of select="ancestor::lzc:table/@friendly-name" />
				<xsl:text> in the database.</xsl:text>
			</xsl:when>
			<xsl:when test="@methodtype = 'Custom'">
				<xsl:value-of select="lzc:description" />
			</xsl:when>
		</xsl:choose>
		<xsl:text>
*/
SET ANSI_NULLS ON
GO

</xsl:text>
		<xsl:text>ALTER PROCEDURE [sp</xsl:text>
		<xsl:value-of select="@db-name" />
		<xsl:text>]</xsl:text>
		<xsl:if test="count( lzc:method-params/lzc:method-param ) &gt; 0">
			<xsl:text>
(		
</xsl:text>
		</xsl:if>
		<xsl:for-each select="lzc:method-params/lzc:method-param">
			<xsl:variable name="ParameterName">
				<xsl:text>@</xsl:text>
				<xsl:if test="ancestor::lzc:project/use-parameter-prefix">p</xsl:if>
				<xsl:value-of select="@dbname" />
			</xsl:variable>
			<xsl:text>	</xsl:text>
			<xsl:value-of select="$ParameterName" />
			<xsl:value-of select="$space" />
			<xsl:call-template name="getSqlParam">
				<xsl:with-param name="pFieldType" select="@type" />
			</xsl:call-template>
			<xsl:if test="@direction = 'out'">
				<xsl:text> OUT</xsl:text>
			</xsl:if>
			<xsl:if test="position() &lt; count( ../lzc:method-param )">
				<xsl:text>,</xsl:text>
			</xsl:if>
			<xsl:text>
</xsl:text>
		</xsl:for-each>
		<xsl:if test="count( lzc:method-params/lzc:method-param ) &gt; 0">
			<xsl:text>
)</xsl:text>
		</xsl:if>
		<xsl:text> AS
		
BEGIN
-- #endlazgen HEADER#

</xsl:text>
		<xsl:variable name="vObjectAlias">
			<xsl:value-of select="ancestor::lzc:table/@alias" />
		</xsl:variable>

		<xsl:choose>
			<xsl:when test="( @methodtype = $METHODTYPE_GET ) or ( @methodtype = $METHODTYPE_LIST )">
				<xsl:text>-- #lazgen BODY#

</xsl:text>
				<xsl:text>SELECT
</xsl:text>
				<xsl:for-each select=".//lzc:method-result">
					<xsl:text>	[</xsl:text>
					<xsl:value-of select="ancestor::lzc:object-ref/@alias" />
					<xsl:text>].[</xsl:text>
					<xsl:value-of select="@dbname" />
					<xsl:text>]</xsl:text>
					<xsl:variable name="RESULT_POSITION" select="position()" />
					<xsl:variable name="RESULT_COUNT" select="count( ancestor::lzc:method-results//lzc:method-result )" />
					<xsl:if test="$RESULT_POSITION &lt; $RESULT_COUNT">
						<xsl:text>,</xsl:text>
					</xsl:if>
					<xsl:text>
</xsl:text>
				</xsl:for-each>
				<xsl:text>FROM</xsl:text>
				<xsl:variable name="TABLE_CONTEXT" select="lzc:method-results/lzc:object-ref[ position() = 1 ]" />
				<xsl:for-each select=".//lzc:object-ref">
					<xsl:text>
	</xsl:text>
					<xsl:if test="position() &gt; 1">
						<xsl:choose>
							<xsl:when test="@required = 'True'">
								<xsl:text>INNER JOIN </xsl:text>
							</xsl:when>
							<xsl:otherwise>
								<xsl:text>LEFT JOIN </xsl:text>
							</xsl:otherwise>
						</xsl:choose>
					</xsl:if>
					<xsl:text>[tbl</xsl:text>
					<xsl:value-of select="@ref" />
					<xsl:text>] [</xsl:text>
					<xsl:value-of select="@alias" />
					<xsl:text>]</xsl:text>
					<xsl:if test="position() &gt; 1">
						<xsl:text> ON [</xsl:text>
						<xsl:value-of select="$TABLE_CONTEXT/@alias" />
						<xsl:text>].[</xsl:text>
						<xsl:value-of select="@fkfield" />
						<xsl:text>] = [</xsl:text>
						<xsl:value-of select="@alias" />
						<xsl:text>].[</xsl:text>
						<xsl:value-of select="@pkfield" />
						<xsl:text>]</xsl:text>
					</xsl:if>
				</xsl:for-each>
				<xsl:text>
</xsl:text>
				<xsl:if test="count( lzc:method-filters/lzc:method-filter ) &gt; 0">
					<xsl:text>WHERE
</xsl:text>
					<xsl:for-each select="lzc:method-filters/lzc:method-filter">
						<xsl:variable name="PARAM_NAME" select="@param" />
						<xsl:variable name="PARAM" select="ancestor::lzc:method/lzc:method-params/lzc:method-param[ @name = $PARAM_NAME ]" />
						<xsl:variable name="ParameterName">
							<xsl:text>@</xsl:text>
							<xsl:if test="ancestor::lzc:project/use-parameter-prefix">p</xsl:if>
							<xsl:value-of select="$PARAM/@dbname" />
						</xsl:variable>
						<xsl:text>	[</xsl:text>
						<xsl:value-of select="$vObjectAlias" />
						<xsl:text>].[</xsl:text>
						<xsl:value-of select="@dbname" />
						<xsl:text>]</xsl:text>
						<xsl:choose>
							<xsl:when test="@value">
								<xsl:variable name="VALUE_FIELD_NAME" select="@field" />
								<xsl:variable name="VALUE_FIELD" select="ancestor::lzc:table/lzc:fields/lzc:field[ @name = $VALUE_FIELD_NAME ]" />

								<xsl:choose>
									<xsl:when test="@value = 'NULL'">
										<xsl:text> IS NULL</xsl:text>
									</xsl:when>
									<xsl:otherwise>
										<xsl:text> = </xsl:text>
										<xsl:choose>
											<xsl:when test="$VALUE_FIELD/@type = $DATATYPE_VARCHAR">
												<xsl:text>'</xsl:text>
												<xsl:value-of select="@value" />
												<xsl:text>'</xsl:text>
											</xsl:when>
											<xsl:otherwise>
												<xsl:value-of select="@value" />
											</xsl:otherwise>
										</xsl:choose>
									</xsl:otherwise>
								</xsl:choose>
							</xsl:when>
							<xsl:otherwise>
								<xsl:text> = </xsl:text>
								<xsl:value-of select="$ParameterName" />
							</xsl:otherwise>
						</xsl:choose>
						<xsl:if test="position() &lt; count( ../lzc:method-filter )">
							<xsl:text> AND</xsl:text>
						</xsl:if>
						<xsl:text>
</xsl:text>
					</xsl:for-each>
				</xsl:if>
				<xsl:text>
-- #endlazgen BODY#

</xsl:text>
			</xsl:when>

			<!-- Create and Update Method Implementation -->
			<xsl:when test="( @methodtype = $METHODTYPE_CREATE ) or ( @methodtype = $METHODTYPE_UPDATE )">
				<xsl:text>-- #lazgen BODY#

</xsl:text>
				<xsl:text>BEGIN TRANSACTION;

</xsl:text>

				<xsl:variable name="CURRENT_METHOD" select="." />
				<xsl:call-template name="CreateSQLStatement">
					<xsl:with-param name="pMethod" select="$CURRENT_METHOD" />
				</xsl:call-template>

				<xsl:text>COMMIT TRANSACTION;
</xsl:text>
				
				<xsl:text>
-- #endlazgen BODY#

</xsl:text>

			</xsl:when>

		</xsl:choose>
		<xsl:text>
-- #lazgen FOOTER#

END

GO

SET ANSI_NULLS OFF
GO   
-- #endlazgen FOOTER#
</xsl:text>
	</xsl:template>

	<xsl:template name="CreateSQLStatement">
		<xsl:param name="pMethod" />

		<xsl:for-each select="$pMethod/lzc:method-effects/lzc:object-ref">
			<xsl:choose>
				<xsl:when test="@effecttype = $METHODTYPE_CREATE">
					<!-- Assign non-Identity Autogen values first -->
					<xsl:for-each select="lzc:fields/lzc:method-effect[ @autogenerate = 'True' and not( @type = $DATATYPE_IDENTITY ) ]">

						<xsl:variable name="ParameterName">
							<xsl:text>@</xsl:text>
							<xsl:if test="ancestor::lzc:project/use-parameter-prefix">p</xsl:if>
							<xsl:value-of select="@param" />
						</xsl:variable>
						<xsl:text>SET </xsl:text>
						<xsl:value-of select="$ParameterName" />
						<xsl:text> = </xsl:text>
						<xsl:choose>
							<xsl:when test="@type = $DATATYPE_GUID">
								<xsl:text>NEWID()</xsl:text>
							</xsl:when>
							<xsl:when test="@type = $DATATYPE_DATETIME">
								<xsl:text>CURRENT_TIMESTAMP</xsl:text>
							</xsl:when>
						</xsl:choose>
						<xsl:text>;
</xsl:text>

					</xsl:for-each>

					<xsl:text>
INSERT [tbl</xsl:text>
					<xsl:value-of select="@ref" />
					<xsl:text>]</xsl:text>
					<xsl:text>
(
</xsl:text>
					<xsl:for-each select="lzc:fields/lzc:method-effect">
						<xsl:text>	[</xsl:text>
						<xsl:value-of select="@dbname" />
						<xsl:text>]</xsl:text>
						<xsl:if test="position() &lt; count( ../lzc:method-effect )">
							<xsl:text>,</xsl:text>
						</xsl:if>
						<xsl:text>
</xsl:text>
					</xsl:for-each>
					<xsl:text>)
VALUES
(
</xsl:text>
					<xsl:for-each select="lzc:fields/lzc:method-effect">
						<xsl:text>   </xsl:text>
						<xsl:variable name="ParameterName">
							<xsl:text>@</xsl:text>
							<xsl:if test="ancestor::lzc:project/use-parameter-prefix">p</xsl:if>
							<xsl:value-of select="@dbname" />
						</xsl:variable>
						<xsl:variable name="PARAM_NAME" select="@param" />
						<xsl:variable name="PARAM" select="ancestor::lzc:method/lzc:method-params/lzc:method-param[ @name = $PARAM_NAME ]" />
						<xsl:value-of select="$ParameterName" />
						<xsl:if test="position() &lt; count( ../lzc:method-effect )">
							<xsl:text>,</xsl:text>
						</xsl:if>
						<xsl:text>
</xsl:text>
					</xsl:for-each>
					<xsl:text>)
</xsl:text>
				</xsl:when>
				<xsl:when test="@effecttype = $METHODTYPE_UPDATE">
					<xsl:text>UPDATE
	[tbl</xsl:text>
					<xsl:value-of select="@ref" />
					<xsl:text>]
SET
</xsl:text>
					<xsl:for-each select="lzc:fields/lzc:method-effect">
						<xsl:variable name="PARAM_NAME" select="@param" />
						<xsl:variable name="PARAM" select="ancestor::lzc:method/lzc:method-params/lzc:method-param[ @name = $PARAM_NAME ]" />
						<xsl:variable name="ParameterName">
							<xsl:text>@</xsl:text>
							<xsl:if test="ancestor::lzc:project/use-parameter-prefix">p</xsl:if>
							<xsl:value-of select="$PARAM/@dbname" />
						</xsl:variable>
						<xsl:text>	[</xsl:text>
						<xsl:value-of select="@dbname" />
						<xsl:text>]</xsl:text>
						<xsl:text> = </xsl:text>
						<xsl:value-of select="$ParameterName" />
						<xsl:if test="position() &lt; count( ../lzc:method-effect )">
							<xsl:text>,</xsl:text>
						</xsl:if>
						<xsl:text>
</xsl:text>
					</xsl:for-each>
					<xsl:if test="count( lzc:method-filters/lzc:method-filter ) &gt; 0">
						<xsl:text>WHERE
</xsl:text>
						<xsl:for-each select="lzc:method-filters/lzc:method-filter">
							<xsl:variable name="PARAM_NAME" select="@param" />
							<xsl:variable name="PARAM" select="ancestor::lzc:method/lzc:method-params/lzc:method-param[ @name = $PARAM_NAME ]" />
							<xsl:variable name="ParameterName">
								<xsl:text>@</xsl:text>
								<xsl:if test="ancestor::lzc:project/use-parameter-prefix">p</xsl:if>
								<xsl:value-of select="$PARAM/@dbname" />
							</xsl:variable>
							<xsl:text>	[</xsl:text>
							<xsl:value-of select="@dbname" />
							<xsl:text>]</xsl:text>
							<xsl:text> = </xsl:text>
							<xsl:value-of select="$ParameterName" />
							<xsl:if test="position() &lt; count( ../lzc:method-filter )">
								<xsl:text> AND</xsl:text>
							</xsl:if>
							<xsl:text>
</xsl:text>
						</xsl:for-each>
					</xsl:if>
					<xsl:text>
</xsl:text>
				</xsl:when>
			</xsl:choose>
			<xsl:text>
			
IF @@ERROR &lt;&gt; 0 
BEGIN
   -- Return 99 to the calling program to indicate failure.
   PRINT 'An error occurred.'
   ROLLBACK TRANSACTION;
   RETURN(99)
END

</xsl:text>
		</xsl:for-each>

		<xsl:for-each select="lzc:method-params/lzc:method-param[ ( @type = $DATATYPE_INT ) and ( @autogenerate = 'True' ) ]">
			<xsl:variable name="ParameterName">
				<xsl:text>@</xsl:text>
				<xsl:if test="ancestor::lzc:project/use-parameter-prefix">p</xsl:if>
				<xsl:value-of select="@name" />
			</xsl:variable>
			<xsl:text>SET </xsl:text>
			<xsl:value-of select="$ParameterName" />
			<xsl:text> = SCOPE_IDENTITY();
		
</xsl:text>

		</xsl:for-each>
	</xsl:template>

</xsl:stylesheet>